|
Author |
Thread Statistics | Show CCP posts - 24 post(s) |
|
CCP DeNormalized
C C P C C P Alliance
18
|
Posted - 2014.10.17 16:24:00 -
[1] - Quote
DB is currently around 2.8 TB, 2 weeks ago it was 3 TB...
We do routine archiving to pull old log records, etc... out to separate databases in an attempt to run as lean as possible.
We also have difference services (sso/vgs/images) in different DB's, so while the core DB itself is around 2 TB (2.8 w/ free space in files for growth), the entire stack is closer to 5-6 TB total. CCP DeNormalized DBA Virtual World Operations |
|
|
CCP DeNormalized
C C P C C P Alliance
20
|
Posted - 2014.10.17 16:28:00 -
[2] - Quote
Chribba wrote:Last time I heard it was a few TB, that was some years ago, so with Jita's daily market transactions I would guess 10x the size now.
/c
Over the course of the last year, the market tables have grown by about 500 GB :) CCP DeNormalized DBA Virtual World Operations |
|
|
CCP DeNormalized
C C P C C P Alliance
21
|
Posted - 2014.10.17 16:59:00 -
[3] - Quote
ISD LackOfFaith wrote:Disclaimer: what follows is a nerd's best guess, not any official figures from CCP.
TL;DR: Roughly 3.5 TB (3500 GB) of data for strictly describing the universe and stuff running. Roughly 100 TB (100000 GB) for total storage across all of TQ. See below for full details.
Great effort LoF!
As mentioned, the core DB is 2.8 TB currently, and the image DB is around 1.3 TB.
the inventory system takes up about 250 GB (a single table w/ 2-3 indexes) the market system takes up around 300 GB (2-3 tables and multiple indexes) in-game mail system is around 300 GB as well (we delete most of chribba's mail just to annoy him :))
CCP DeNormalized DBA Virtual World Operations |
|
|
CCP DeNormalized
C C P C C P Alliance
22
|
Posted - 2014.10.17 17:08:00 -
[4] - Quote
TheMercenaryKing wrote:
How often do you backup the servers? Daily via deduplication?
I can't imagine your RAID setup so the throughput must be pretty good for all the transactions. SSDs or a lot of 15K drives? 15k drives have about 175 IOPS at 4K block size i think. I doubt you are using 4K, probably another block size for optimization.
DB is split across 2 tiers of storage, SSD (enterprise level intel disks in v7000 enclosures) for the heavily accessed stuff and 15k SAS disks for the second 'slower' tier.
I'm not that knowledgeable abuot the SAN side and how the block sizes are handed there, but as the LUN's are given to us, we setup our volumes @ 64K block size as this is the optimal allocation for SQL (64k = 1 extent = 8x8KB pages)
We do 2 full backups per day (written across 8 files for disk melting IO dumps) which take a little over 1 hour each... Hits the disks for around 400 MB/s
CCP DeNormalized DBA Virtual World Operations |
|
|
CCP DeNormalized
C C P C C P Alliance
22
|
Posted - 2014.10.17 17:12:00 -
[5] - Quote
MiliasColds wrote:it's not on an SSD it's on RAMSAN AFAIK, so think more like 100k / TB (i think) but hey it's super super fast :P
edi: seems i was wrong and beaten to the punch by 1s
We used to run on the RamSan's but stopped about 3 years ago. They were completely overkill for our needs and at the time expanding was rather difficult.
We had 3 units, 2x 128 GB pure flashbased units and sometime later added a third 2 TB array (this wasn't flash based if I remember).
CCP DeNormalized DBA Virtual World Operations |
|
|
CCP DeNormalized
C C P C C P Alliance
26
|
Posted - 2014.10.17 17:44:00 -
[6] - Quote
virm pasuul wrote:I've recently started to look after a work database that's 24Gb and growing by 100Mbish per day. It's a tiny drop in the ocean compared to Eve but I keep looking at it and thinking "How can I stop it growing?" I am considering turning the users off because they are spoiling my database.
we discuss similar things :)
An on-going project is trimming the market orders table - we tried to talk the game designers into just letting us truncate the table but they wanted no part of it :) CCP DeNormalized DBA Virtual World Operations |
|
|
CCP DeNormalized
C C P C C P Alliance
32
|
Posted - 2014.10.17 18:02:00 -
[7] - Quote
Caviar Liberta wrote:TheMercenaryKing wrote:CCP DeNormalized wrote:DB is currently around 2.8 TB, 2 weeks ago it was 3 TB...
We do routine archiving to pull old log records, etc... out to separate databases in an attempt to run as lean as possible.
We also have difference services (sso/vgs/images) in different DB's, so while the core DB itself is around 2 TB (2.8 w/ free space in files for growth), the entire stack is closer to 5-6 TB total. How often do you backup the servers? Daily via deduplication? I can't imagine your RAID setup so the throughput must be pretty good for all the transactions. SSDs or a lot of 15K drives? 15k drives have about 175 IOPS at 4K block size i think. I doubt you are using 4K, probably another block size for optimization. The intial back up would be the most time consuming. Setting up the back to only copy any files that has been changed or added would make the task a lot easier then.
we do different things w/ each backup - the secondary one gets shipped to a different server for reporting purposes, but you are correct, a differential would make sense in most cases here CCP DeNormalized DBA Virtual World Operations |
|
|
CCP DeNormalized
C C P C C P Alliance
32
|
Posted - 2014.10.17 18:04:00 -
[8] - Quote
virm pasuul wrote:CCP DeNormalized wrote:virm pasuul wrote:I've recently started to look after a work database that's 24Gb and growing by 100Mbish per day. It's a tiny drop in the ocean compared to Eve but I keep looking at it and thinking "How can I stop it growing?" I am considering turning the users off because they are spoiling my database. we discuss similar things :) An on-going project is trimming the market orders table - we tried to talk the game designers into just letting us truncate the table but they wanted no part of it :) Make everything on the market a fixed price, then the table will become much smaller. Seems a good compromise to me :)
In this market order clean up, we noticed that there is one crazy player that has an open order for 7 years now!!!! Any order expires after 90 days unless you update it...
So this player has been modifying this one order, at least once every 90 days, for the last 7 frickin years!! haha
There are 3 items left in his sell order - I wanted to just go buy them so there wouldn't be such a big gap in ID's :)
I got over ruled on that as well
(i sent a mail to community about this guy saying he (or she?) should get some kinda medal, so we'll see what comes of that) CCP DeNormalized DBA Virtual World Operations |
|
|
CCP DeNormalized
C C P C C P Alliance
40
|
Posted - 2014.10.17 18:39:00 -
[9] - Quote
it is a sell order, that started with 20 units - 17 sold and 3 left :)
it's only like 1.6 mil per unit and not the highest in the region. CCP DeNormalized DBA Virtual World Operations |
|
|
CCP DeNormalized
C C P C C P Alliance
44
|
Posted - 2014.10.17 19:07:00 -
[10] - Quote
virm pasuul wrote:Hey DeNormalized when is CCP going to set up a database administrator's player event? We could all drink beer and complain about users touching the database.
It could be the only Eve player event ever where no one was allowed to log onto Eve.
We do it every year @ fanfest during our virtual worlds admin round table/QandA :)
CCP DeNormalized DBA Virtual World Operations |
|
|
|
CCP DeNormalized
C C P C C P Alliance
65
|
Posted - 2014.10.18 13:26:00 -
[11] - Quote
Toriessian wrote:Jandice Ymladris wrote:CCP DeNormalized wrote:it is a sell order, that started with 20 units - 17 sold and 3 left :)
it's only like 1.6 mil per unit and not the highest in the region. Must be something very rarely traded then! Or perhaps the creator just likes to see how long he can keep running it... Still, darn impressive lasting that long. Its about the right price for synth boosters.
Perhaps, but this isn't those!! :) CCP DeNormalized DBA Virtual World Operations |
|
|
CCP DeNormalized
C C P C C P Alliance
86
|
Posted - 2014.10.19 12:24:00 -
[12] - Quote
Sven Viko VIkolander wrote:CCP DeNormalized wrote:it is a sell order, that started with 20 units - 17 sold and 3 left :)
it's only like 1.6 mil per unit and not the highest in the region. This should be like a treasure hunt, where we go and try to find this item. Since there's not much isk to be made on a 1.6m item I would guess that this is in null and is maybe a cyno generator, on the market for availability in the region and not for making isk. That's my guess anyhow, as any other explanation seems completely irrational lol..
I passed the info onto community in hopes something like might happen :) But we'll see how they wanna handle it. CCP DeNormalized DBA Virtual World Operations |
|
|
CCP DeNormalized
C C P C C P Alliance
86
|
Posted - 2014.10.19 12:26:00 -
[13] - Quote
Rakshasa Taisab wrote:CCP should send a concerned citizen report to the local authorities so that mental health professionals can check in on that guy.
I'm in a little more cynical :)
I also passed it onto security so they could check if it was some long forgotten about market bot sitting in someones closet praying for someone to buy the damn things so it could end itself :) CCP DeNormalized DBA Virtual World Operations |
|
|
CCP DeNormalized
C C P C C P Alliance
104
|
Posted - 2014.10.19 22:04:00 -
[14] - Quote
Jandice Ymladris wrote: I wonder how much data our assets are occupying on the server? As unlike other MMO's, we can have stuff everywhere indefinatly, so I suspect it would be a big drain on data storage? Or not really as it's more static?
the inventory system is around 250 GB - this includes multiple indexes on the same data though so we (or you) can access it faster, etc...
every item/stack per location has a unique row in the DB, for a total of around 2.2 billion rows at the moment :)
CCP DeNormalized DBA Virtual World Operations |
|
|
CCP DeNormalized
C C P C C P Alliance
114
|
Posted - 2014.10.20 10:28:00 -
[15] - Quote
Varisto wrote:So you run on SSD that database? Now in curious how long does it take you to kill one drive? SSD dont usually like very much on constantly changing data...
I've asked our SAN guys that very question in the past :)
They say upwards of 5 year life span depending on how many writes, etc... keep in mind these are enterprise grade SSD's (with the insane prices to boot) and have extra 'stuff' to keep them alive longer :)
100% moar stuff!!! yeaaa!!!! hehe
We've yet to lose one, but I believe we're getting close to end of life and may likely replace in the coming year or so.
CCP DeNormalized DBA Virtual World Operations |
|
|
CCP DeNormalized
C C P C C P Alliance
114
|
Posted - 2014.10.20 10:40:00 -
[16] - Quote
Tzeentchful Nakrar wrote: That is a lot of rows. You guys must have some really insane servers to serve that amount of data at the seed you do. I do have a quick question. What is the oldest item a players has?
The most insane part of our DB is ram IMO - we're running 512 GB of ram, so it's able to pull a tremendous amount of data into memory and that gives us great speed.
That much RAM coupled with fast SSD's (and properly indexed data) and you can do quite a bit :)
In terms of oldest item, it's a very difficult thing to find. Up until 4 years ago or so, we were recycling ID's (32 bit) so we are really able to say beyond that.
Now we use 64 bit and can trace how old items are, but I think it's fair to say that there are items in hangars from the beginning :) CCP DeNormalized DBA Virtual World Operations |
|
|
CCP DeNormalized
C C P C C P Alliance
119
|
Posted - 2014.10.20 11:25:00 -
[17] - Quote
Johan Civire wrote:This is good reading stuff :D i like it. But about that order can we have a name or what do he or she sells or is that forbidden? i like to know now ? :D
I wouldn't tell, no... community may choose to do something with this info, but if not it'll just be one more mystery of the universe :) CCP DeNormalized DBA Virtual World Operations |
|
|
CCP DeNormalized
C C P C C P Alliance
119
|
Posted - 2014.10.20 11:43:00 -
[18] - Quote
Kousaka Otsu Shigure wrote:One of my side projects is creating an out-of-game contacts list. Is our ingame contacts list cap somehow related to how the database works?
I'm at ~24k hisec contacts at the moment and i must admit, im such a noob with databases.
In most cases any sort of cap would be done for performance reasons, either due to the DB or the client itself. I can't see the DB being an issue here however.
Mind you, if you were to have 24,000 contacts in-game, i would think the overhead of loading those in the client and doing whatever happens with those, would definitely have a negative impact on client side performance :)
CCP DeNormalized DBA Virtual World Operations |
|
|
CCP DeNormalized
C C P C C P Alliance
157
|
Posted - 2014.12.09 21:40:28 -
[19] - Quote
Amose Amanum wrote:Since the CREST market data is now available on Tranquility, I expect this will become public knowledge soon, and I want the bragging rights for figuring it out weeks ago ;)
The oldest current sell order in Eve is for a pair of Quad Light Beam Laser II on sale at Karan VIII - Royal Amarr Institute School. The vendor is Kroma BaSyl. The sell order ID is 537910304, placing its vintage at around October 2007. I purchased the third-last one to verify these details, then notified Kroma so he could jack up the price from 1.6m to 165m.
nice work Amose!
CCP DeNormalized
DBA
Virtual World Operations
|
|
|
CCP DeNormalized
C C P C C P Alliance
157
|
Posted - 2014.12.09 21:45:30 -
[20] - Quote
Rekindle wrote:CCP DeNormalized wrote:DB is currently around 2.8 TB, 2 weeks ago it was 3 TB...
We do routine archiving to pull old log records, etc... out to separate databases in an attempt to run as lean as possible.
We also have difference services (sso/vgs/images) in different DB's, so while the core DB itself is around 2 TB (2.8 w/ free space in files for growth), the entire stack is closer to 5-6 TB total. ive always been impressed that you picked MSSQL. Your partioning scheme must be insane.
For us, the only thing we use partitions for are sliding window deletes on sequential log tables.
Being able to simply swap out a huge number of rows covering the date range and drop that with almost no IO is great.
CCP DeNormalized
DBA
Virtual World Operations
|
|
|
|
CCP DeNormalized
C C P C C P Alliance
172
|
Posted - 2014.12.10 09:43:38 -
[21] - Quote
Altessa Post wrote:Interesting read, thank you very much!
(I have been several times at fanfest but this is the first time I hear about an admin round table. Dang...)
Altessa, It's usually called something like Virtual Worlds round table or something along those lines.
CCP DeNormalized
DBA
Virtual World Operations
|
|
|
CCP DeNormalized
C C P C C P Alliance
172
|
Posted - 2014.12.10 09:45:04 -
[22] - Quote
Bienator II wrote:can you guys please backup my char on a floppy disk and send it to me?
It would be funny to ship a 5 1/4 to the DC and ask the on-hands support staff to go plug it into one of our servers :)
CCP DeNormalized
DBA
Virtual World Operations
|
|
|
|
|